/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : 127.0.0.1:3306
 Source Schema         : mytest

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 15/05/2020 11:56:06
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_pay_log
-- ----------------------------
DROP TABLE IF EXISTS `t_pay_log`;
CREATE TABLE `t_pay_log`  (
  `order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '说明',
  `orig_order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号',
  `mcht_cd` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '原订单号',
  `tran_cd` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商户号',
  `prod_cd` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易码',
  `biz_cd` char(7) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品码',
  `tran_dt_tm` char(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易时间',
  `txn_date` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易日期',
  `sys_order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易日期',
  `pay_order_id` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易系统订单号',
  `pay_state` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付订单号或者支付url',
  `buyer_user` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单支付状态：00-未支付 01-已支付 02-已冲正 03-已退货',
  `inner_ret_cd` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '响应码（内部）',
  `inner_ret_msg` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '响应描述（内部）',
  `ret_cd` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户登陆名',
  `ret_msg` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '响应码',
  `version` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '响应描述',
  `ins_id_cd` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '版本号',
  `auth_code` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收单机构机构号',
  `tran_amt` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '授权码',
  `refund_reason` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易金额，单位分',
  `retry_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '退款原因,退款时赋值',
  `pay_time` char(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '重试标志:N-无需重试 Y-需要重试支付时间,YYYYMMDDHHMMSS',
  `pay_qrcode` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付二维码',
  `create_ts` datetime(0) NOT NULL COMMENT '创建时间',
  `update_ts` timestamp(0) NULL DEFAULT NULL COMMENT '更新时间',
  `app_order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '前端订单号',
  `appid` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '前端接入应用id',
  `biz_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '业务大类:bankcard.银行卡业务，wx.微信业务，alipay.支付宝业务',
  `cancel_flag` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '撤销、关闭标志(0-不支持 1-支持)',
  `refund_flag` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '退款标志(0-不支持 1-支持)',
  `cashier_user_no` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '收银员登录账号',
  `gps` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'gps',
  `trans_chk_in` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易勾兑标志,0：未勾兑',
  `mcht_stlm_flg` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商户清算标志,0-以平台为准',
  `orig_app_order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '原前端订单号',
  `txn_time` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易时间（无卡系统交易模块生成）',
  `chk_in_key` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '流水勾兑,对账key',
  `buyer_user_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '付款人支付宝id或者微信openid',
  `stlm_date` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '清算日期',
  `refund_amt` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '累计退款金额分',
  `app_tran_cd` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '前端交易码',
  `chnl_order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易渠道流水号，产品平台',
  `fwd_ins_id_cd` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '交易转送机构号',
  `revoke_flag` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '撤销标志（0-不支持 1-支持）',
  `orig_sys_order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '原交易商户单号(退款、撤销交易)',
  `comment` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `scance` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '场景',
  `bill_order_id` varchar(48) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`, `create_ts`) USING BTREE,
  UNIQUE INDEX `ix_pay_log_sys_order_id`(`sys_order_id`, `create_ts`) USING BTREE,
  INDEX `ix_pay_log_mcht_cd`(`mcht_cd`) USING BTREE,
  INDEX `ix_pay_log_app_order_id`(`appid`, `app_order_id`) USING BTREE,
  INDEX `ix_pay_log_mcht_txn_date`(`mcht_cd`, `txn_date`) USING BTREE,
  INDEX `ix_pay_log_mcht_stlm_date`(`mcht_cd`, `stlm_date`) USING BTREE,
  INDEX `ix_pay_log_ins_id_cd`(`ins_id_cd`) USING BTREE,
  INDEX `ix_pay_log_ins_id_cd_txn_date`(`ins_id_cd`, `txn_date`) USING BTREE,
  INDEX `ix_pay_log_ins_id_cd_stlm_date`(`ins_id_cd`, `stlm_date`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci
/*!50100 PARTITION BY RANGE (to_days(create_ts))
(PARTITION p202000 VALUES LESS THAN (737790) ENGINE = InnoDB,
 PARTITION p202001 VALUES LESS THAN (737821) ENGINE = InnoDB,
 PARTITION p202002 VALUES LESS THAN (737850) ENGINE = InnoDB,
 PARTITION p202003 VALUES LESS THAN (737881) ENGINE = InnoDB,
 PARTITION p202004 VALUES LESS THAN (737911) ENGINE = InnoDB,
 PARTITION p202005 VALUES LESS THAN (737942) ENGINE = InnoDB) */;

-- ----------------------------
-- Records of t_pay_log
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Procedure structure for proc_create_paratition
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_create_paratition`;
delimiter ;;
CREATE PROCEDURE `proc_create_paratition`(IN `tableName` VARCHAR(128) CHARSET utf8, 
                                   IN `timeColName` VARCHAR(128) CHARSET utf8)
BEGIN
    DECLARE p_id int;
    DECLARE nextDate date;
    DECLARE lastDate LONG;
    DECLARE p_name varchar(16);
    
    SELECT COUNT(partition_name) INTO p_id 
    FROM INFORMATION_SCHEMA.partitions
    WHERE TABLE_NAME=tableName;
    
    IF p_id=0 THEN
        
        SELECT DATE_ADD(CURDATE()-DAY(CURDATE())+1,INTERVAL 1 MONTH) INTO nextDate  
        FROM DUAL;
 
        SET p_name=DATE_FORMAT(CURDATE(),'%Y%m');
        SET @v_add=CONCAT('ALTER table ',tableName
                ,' PARTITION by range(TO_DAYS('
                , timeColName
                ,'))(partition '
                , CONCAT('p',p_name)
                ,' values less than (TO_DAYS("'
                , nextDate,'")))');
 
   ELSE
 
        SELECT max(partition_description) des INTO lastDate 
        FROM INFORMATION_SCHEMA.partitions 
        WHERE TABLE_NAME=tableName;
 
        SELECT DATE_ADD(FROM_DAYS(lastDate),INTERVAL 1 MONTH) INTO nextDate 
        FROM DUAL;
 
        SET p_name=DATE_FORMAT(FROM_DAYS(lastDate),'%Y%m');
        SET @v_add=CONCAT('alter table ',tableName
                ,' add partition (partition '
                , CONCAT('p',p_name)
                ,' values less than (TO_DAYS("'
                ,nextDate,'")))');
    END IF; 
		select @v_add;
 
     PREPARE stmt from @v_add;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
END
;;
delimiter ;

-- ----------------------------
-- Event structure for event_create_tpaylog_Paratition
-- ----------------------------
DROP EVENT IF EXISTS `event_create_tpaylog_Paratition`;
delimiter ;;
CREATE EVENT `event_create_tpaylog_Paratition`
ON SCHEDULE
EVERY '1' MONTH STARTS '2020-01-01 00:00:00'
DO call proc_create_paratition("t_pay_log","create_ts")
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
